﻿// Admin.NET 项目的版权、商标、专利和其他相关权利均受相应法律法规的保护。使用本项目应遵守相关法律法规和许可证的要求。
//
// 本项目主要遵循 MIT 许可证和 Apache 许可证（版本 2.0）进行分发和使用。许可证位于源代码树根目录中的 LICENSE-MIT 和 LICENSE-APACHE 文件。
//
// 不得利用本项目从事危害国家安全、扰乱社会秩序、侵犯他人合法权益等法律法规禁止的活动！任何基于本项目二次开发而产生的一切法律纠纷和责任，我们不承担任何责任！

using Admin.NET.Application.Entity;
using Admin.NET.Core.Service;
using AngleSharp.Dom;
using Furion.DatabaseAccessor;
using Furion.FriendlyException;
using Mapster;
using Microsoft.AspNetCore.Http;
using SqlSugar;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
namespace Admin.NET.Application;

/// <summary>
/// 号船分析服务 🧩
/// </summary>
[ApiDescriptionSettings(ApplicationConst.GroupName, Order = 100)]
public partial class PlanSharpService : IDynamicApiController, ITransient
{
    private readonly SqlSugarRepository<PlanSharp> _planSharpRep;
    private readonly ISqlSugarClient _sqlSugarClient;

    public PlanSharpService(SqlSugarRepository<PlanSharp> planSharpRep, ISqlSugarClient sqlSugarClient)
    {
        _planSharpRep = planSharpRep;
        _sqlSugarClient = sqlSugarClient;
    }

    /// <summary>
    /// 获取船号详细统计 📊
    /// </summary>
    /// <param name="ch">船号</param>
    /// <returns></returns>
    [DisplayName("获取船号详细统计")]
    [ApiDescriptionSettings(Name = "GetShipDetails"), HttpGet]
    public async Task<List<PlanSharpDetailView>> GetShipDetails([FromQuery] string ch)
    {
        if (string.IsNullOrWhiteSpace(ch))
            return new List<PlanSharpDetailView>();

        // 按船号和分类分析，总计使用实际记录数，完工数使用实际完工记录数
        var sql = @"
            SELECT 
                pl.Ch AS Ch,
                pt.Name AS TypeName, 
                COUNT(*) AS Total, 
                SUM(CASE WHEN pl.ActualEnd IS NOT NULL THEN 1 ELSE 0 END) AS CompletedCount,
                CASE WHEN COUNT(*) = 0 THEN 0 
                     ELSE ROUND(SUM(CASE WHEN pl.ActualEnd IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) 
                END AS Progress,
                MIN(pl.ActualStart) AS EarliestStartDate,
                MAX(pl.ActualEnd) AS LatestEndDate
            FROM plan_List pl
            INNER JOIN plan_Type pt ON pl.ClassName = pt.Id
            INNER JOIN plan_Sharp ps ON pl.Ch = ps.Ch
            WHERE pl.Ch = @Ch AND pl.IsDelete = 0 AND ps.IsDelete = 0
            GROUP BY pl.Ch, pt.Name, pt.OrderNo
            ORDER BY pt.OrderNo";

        return await _sqlSugarClient.SqlQueryable<PlanSharpDetailView>(sql)
            .AddParameters(new { Ch = ch })
            .ToListAsync();
    }

    /// <summary>
    /// 分页查询号船分析 🔖
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [DisplayName("分页查询号船分析")]
    [ApiDescriptionSettings(Name = "Page"), HttpPost]
    public async Task<SqlSugarPagedList<PlanSharpOutput>> Page(PagePlanSharpInput input)
    {
        input.Keyword = input.Keyword?.Trim();
        var query = _planSharpRep.AsQueryable()
            .WhereIF(!string.IsNullOrWhiteSpace(input.Keyword), u =>
                u.Ch.Contains(input.Keyword) ||
                (u.Dock != null && u.Dock.Contains(input.Keyword)) ||
                (u.Batch != null && u.Batch.Contains(input.Keyword)) ||
                (u.ClassificationSociety != null && u.ClassificationSociety.Contains(input.Keyword)) ||
                (u.ShipType != null && u.ShipType.Contains(input.Keyword)) ||
                (u.ShipOwner != null && u.ShipOwner.Contains(input.Keyword)) ||
                (u.DockBefore != null && u.DockBefore.Contains(input.Keyword)) ||
                (u.DockAfter != null && u.DockAfter.Contains(input.Keyword))
            )
            .WhereIF(!string.IsNullOrWhiteSpace(input.Dock), u => u.Dock.Contains(input.Dock.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.Batch), u => u.Batch != null && u.Batch.Contains(input.Batch.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.ClassificationSociety), u => u.ClassificationSociety != null && u.ClassificationSociety.Contains(input.ClassificationSociety.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.ShipType), u => u.ShipType != null && u.ShipType.Contains(input.ShipType.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.ShipOwner), u => u.ShipOwner != null && u.ShipOwner.Contains(input.ShipOwner.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.DockBefore), u => u.DockBefore != null && u.DockBefore.Contains(input.DockBefore.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.DockAfter), u => u.DockAfter != null && u.DockAfter.Contains(input.DockAfter.Trim()))
            .WhereIF(!string.IsNullOrWhiteSpace(input.Ch), u => u.Ch.Contains(input.Ch.Trim()))
            .WhereIF(input.StatusAll.HasValue, u => u.StatusAll == input.StatusAll)
            .OrderBy(u => u.Dock)
            .OrderBy(u => u.StartSharpTime)
            .OrderBy(u => u.Ch)
            .Select<PlanSharpOutput>();
		return await query.OrderBuilder(input).ToPagedListAsync(input.Page, input.PageSize);
    }

    /// <summary>
    /// 获取号船分析详情 ℹ️
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [DisplayName("获取号船分析详情")]
    [ApiDescriptionSettings(Name = "Detail"), HttpGet]
    public async Task<PlanSharp> Detail([FromQuery] QueryByIdPlanSharpInput input)
    {
        return await _planSharpRep.GetFirstAsync(u => u.Id == input.Id);
    }

    /// <summary>
    /// 增加号船分析 ➕
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [DisplayName("增加号船分析")]
    [ApiDescriptionSettings(Name = "Add"), HttpPost]
    public async Task<long> Add(AddPlanSharpInput input)
    {
        var entity = input.Adapt<PlanSharp>();
        
        // 如果开头是数字则添加H前缀
        if (!string.IsNullOrWhiteSpace(entity.Ch) && char.IsDigit(entity.Ch[0]))
        {
            entity.Ch = "H" + entity.Ch;
        }
        
        // 自动计算外协分段数
        if (entity.FdTotal.HasValue && entity.SwsInt.HasValue && !entity.OtherInt.HasValue) 
        {
            entity.OtherInt = entity.FdTotal - entity.SwsInt;
        }
        
        // 处理数据库不允许NULL的字段，如果业务字段为空则提供默认值
        // 根据错误信息，FirstStrTime字段数据库不允许NULL
        if (!entity.FirstStrTime.HasValue)
        {
            entity.FirstStrTime = entity.StartSharpTime; // 使用开工时间作为默认值
        }
        
        // 如果数据库的FirstPStrTime也不允许NULL，同样处理
        if (!entity.FirstPStrTime.HasValue)
        {
            entity.FirstPStrTime = entity.StartSharpTime; // 使用开工时间作为默认值
        }
        
        if (await _planSharpRep.IsAnyAsync(u => u.Ch != null && u.Ch == entity.Ch)) throw Oops.Oh("船号已存在");
        var result = await _planSharpRep.InsertAsync(entity);
        if (result)
        {
            // 插入后计算并更新进度
            await UpdateShipProgress(entity.Ch);
            return entity.Id;
        }
        return 0;
    }

    /// <summary>
    /// 更新号船分析 ✏️
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [DisplayName("更新号船分析")]
    [ApiDescriptionSettings(Name = "Update"), HttpPost]
    public async Task Update(UpdatePlanSharpInput input)
    {
        if (await _planSharpRep.IsAnyAsync(u => u.Id != input.Id && u.Ch != null && u.Ch == input.Ch)) throw Oops.Oh("船号已存在");
        var entity = input.Adapt<PlanSharp>();
        await _planSharpRep.AsUpdateable(entity)
        .IgnoreColumns(u => new {
            u.OtherInt,
            u.Jindu,
        })
        .ExecuteCommandAsync();
        
        // 更新后重新计算进度
        await UpdateShipProgress(input.Ch);
    }

    /// <summary>
    /// 删除号船分析 ❌
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [DisplayName("删除号船分析")]
    [ApiDescriptionSettings(Name = "Delete"), HttpPost]
    public async Task Delete(DeletePlanSharpInput input)
    {
        var entity = await _planSharpRep.GetFirstAsync(u => u.Id == input.Id) ?? throw Oops.Oh(ErrorCodeEnum.D1002);
        await _planSharpRep.FakeDeleteAsync(entity);   //假删除
        //await _planSharpRep.DeleteAsync(entity);   //真删除
    }

    /// <summary>
    /// 批量删除号船分析 ❌
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [DisplayName("批量删除号船分析")]
    [ApiDescriptionSettings(Name = "BatchDelete"), HttpPost]
    public async Task<int> BatchDelete([Required(ErrorMessage = "主键列表不能为空")]List<DeletePlanSharpInput> input)
    {
        var exp = Expressionable.Create<PlanSharp>();
        foreach (var row in input) exp = exp.Or(it => it.Id == row.Id);
        var list = await _planSharpRep.AsQueryable().Where(exp.ToExpression()).ToListAsync();
   
        return await _planSharpRep.FakeDeleteAsync(list);   //假删除
        //return await _planSharpRep.DeleteAsync(list);   //真删除
    }
    
    /// <summary>
    /// 导出号船分析记录 🔖
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [DisplayName("导出号船分析记录")]
    [ApiDescriptionSettings(Name = "Export"), HttpPost, NonUnify]
    public async Task<IActionResult> Export(PagePlanSharpInput input)
    {
        var list = (await Page(input)).Items?.Adapt<List<ExportPlanSharpOutput>>() ?? new();
        if (input.SelectKeyList?.Count > 0) list = list.Where(x => input.SelectKeyList.Contains(x.Id)).ToList();
        return ExcelHelper.ExportTemplate(list, "号船分析导出记录");
    }
    
    /// <summary>
    /// 下载号船分析数据导入模板 ⬇️
    /// </summary>
    /// <returns></returns>
    [DisplayName("下载号船分析数据导入模板")]
    [ApiDescriptionSettings(Name = "Import"), HttpGet, NonUnify]
    public IActionResult DownloadTemplate()
    {
        return ExcelHelper.ExportTemplate(new List<ExportPlanSharpOutput>(), "号船分析导入模板");
    }
    
    private static readonly object _planSharpImportLock = new object();
    /// <summary>
    /// 导入号船分析记录 💾
    /// </summary>
    /// <returns></returns>
    [DisplayName("导入号船分析记录")]
    [ApiDescriptionSettings(Name = "Import"), HttpPost, NonUnify, UnitOfWork]
    public IActionResult ImportData([Required] IFormFile file)
    {
        lock (_planSharpImportLock)
        {
            var stream = ExcelHelper.ImportData<ImportPlanSharpInput, PlanSharp>(file, (list, markerErrorAction) =>
            {
                _sqlSugarClient.Utilities.PageEach(list, 2048, pageItems =>
                {
                    
                    // 校验并过滤必填基本类型为null的字段
                    var rows = pageItems.Where(x => {
                        if (!string.IsNullOrWhiteSpace(x.Error)) return false;
                        if (x.FdTotal == null){
                            x.Error = "分段量不能为空";
                            return false;
                        }
                        if (x.SwsInt == null){
                            x.Error = "本部不能为空";
                            return false;
                        }
                        // 自动计算外协分段数
                        if (x.FdTotal.HasValue && x.SwsInt.HasValue && !x.OtherInt.HasValue)
                        {
                            x.OtherInt = x.FdTotal - x.SwsInt;
                        }
                        return true;
                    }).Adapt<List<PlanSharp>>();
                    
                    var storageable = _planSharpRep.Context.Storageable(rows)
                        .SplitError(it => string.IsNullOrWhiteSpace(it.Item.Ch), "船号不能为空")
                        .SplitError(it => it.Item.Ch?.Length > 32, "船号长度不能超过32个字符")
                        .SplitError(it => it.Item.OffDockTime == null, "下坞不能为空")
                        .SplitError(it => it.Item.FloatTime == null, "起浮不能为空")
                        .SplitError(it => it.Item.FdTotal == null, "分段量不能为空")
                        .SplitError(it => it.Item.SwsInt == null, "本部不能为空")
                        .WhereColumns(it => new { it.Ch })
                        .SplitInsert(it=> !it.Any())
                        .SplitUpdate(it=> it.Any())
                        .ToStorage();
                    
                    storageable.AsInsertable.ExecuteCommand();// 不存在插入
                    storageable.AsUpdateable.UpdateColumns(it => new
                    {
                        it.Dock,
                        it.Batch,
                        it.ClassificationSociety,
                        it.ShipType,
                        it.ShipOwner,
                        it.DockBefore,
                        it.DockAfter,
                        it.Ch,
                        it.StartSharpTime,
                        it.OffDockTime,
                        it.FloatTime,
                        it.FdTotal,
                        it.SwsInt,
                        it.OtherInt,
                        it.FirstStrTime,
                        it.LastEndTime,
                        it.Jindu,
                        it.StatusAll,
                    }).ExecuteCommand();// 存在更新
                    
                    // 标记错误信息
                    markerErrorAction.Invoke(storageable, pageItems, rows);
                });
            });
            
            return stream;
        }
    }

    /// <summary>
    /// 更新船号进度 🔄
    /// </summary>
    /// <param name="ch">船号</param>
    /// <returns></returns>
    private async Task UpdateShipProgress(string ch)
    {
        if (string.IsNullOrWhiteSpace(ch))
            return;

        // 查询进度信息：完工数来自分段报验，总数使用本部数量
        var progressSql = @"
            SELECT 
                ps.SwsInt AS Total,
                SUM(CASE WHEN pl.ActualEnd IS NOT NULL THEN 1 ELSE 0 END) AS Completed
            FROM plan_List pl
            INNER JOIN plan_Type pt ON pl.ClassName = pt.Id
            INNER JOIN plan_Sharp ps ON pl.Ch = ps.Ch
            WHERE pl.Ch = @Ch AND pl.IsDelete = 0 AND ps.IsDelete = 0 AND pt.Name = '分段报验'
            GROUP BY ps.SwsInt";

        // 查询该船号的最早开工和最后结束日期（只使用实际日期）
        var dateSql = @"
            SELECT 
                MIN(pl.ActualStart) AS EarliestStart,
                MAX(pl.ActualEnd) AS LatestEnd
            FROM plan_List pl
            WHERE pl.Ch = @Ch AND pl.IsDelete = 0 
                AND (pl.ActualStart IS NOT NULL OR pl.ActualEnd IS NOT NULL)";

        // 查询该船号的最早计划时间
        var planTimeSql = @"
            SELECT 
                MIN(pl.PlanStart) AS EarliestPlanStart
            FROM plan_List pl
            WHERE pl.Ch = @Ch AND pl.IsDelete = 0 AND pl.PlanStart IS NOT NULL";

        var progressResult = await _sqlSugarClient.SqlQueryable<dynamic>(progressSql)
            .AddParameters(new { Ch = ch })
            .FirstAsync();

        var dateResult = await _sqlSugarClient.SqlQueryable<dynamic>(dateSql)
            .AddParameters(new { Ch = ch })
            .FirstAsync();

        var planTimeResult = await _sqlSugarClient.SqlQueryable<dynamic>(planTimeSql)
            .AddParameters(new { Ch = ch })
            .FirstAsync();

        if (progressResult != null && dateResult != null)
        {
            int total = Convert.ToInt32(progressResult.Total ?? 0);
            int completed = Convert.ToInt32(progressResult.Completed ?? 0);
            
            decimal progress = 0;
            if (total > 0)
            {
                // 计算分段报验进度百分比
                progress = Math.Round((decimal)completed * 100 / total, 2);
            }
            
            // 获取最早开工、最后结束日期和最早计划时间
            DateTime? earliestStart = null;
            DateTime? latestEnd = null;
            DateTime? earliestPlanStart = null;
            
            try
            {
                if (dateResult.EarliestStart != null)
                {
                    earliestStart = Convert.ToDateTime(dateResult.EarliestStart);
                }
                
                if (dateResult.LatestEnd != null)
                {
                    latestEnd = Convert.ToDateTime(dateResult.LatestEnd);
                }
                
                if (planTimeResult.EarliestPlanStart != null)
                {
                    earliestPlanStart = Convert.ToDateTime(planTimeResult.EarliestPlanStart);
                }
            }
            catch (Exception ex)
            {
                // 尝试备用转换方法
                if (dateResult.EarliestStart != null)
                {
                    if (DateTime.TryParse(dateResult.EarliestStart.ToString(), out DateTime startDate))
                    {
                        earliestStart = startDate;
                    }
                }
                
                if (dateResult.LatestEnd != null)
                {
                    if (DateTime.TryParse(dateResult.LatestEnd.ToString(), out DateTime endDate))
                    {
                        latestEnd = endDate;
                    }
                }
                
                if (planTimeResult.EarliestPlanStart != null)
                {
                    if (DateTime.TryParse(planTimeResult.EarliestPlanStart.ToString(), out DateTime planStartDate))
                    {
                        earliestPlanStart = planStartDate;
                    }
                }
            }
            
            
            // 更新plan_Sharp表中的进度和日期字段
            // 构建更新对象，包含最早计划时间
            var updateObject = new PlanSharp { Jindu = progress };
            
            // 如果有最早计划时间，则更新
            if (earliestPlanStart.HasValue)
            {
                updateObject.FirstPStrTime = earliestPlanStart.Value;
            }
            
            // 如果有实际日期，则更新
            if (earliestStart.HasValue && latestEnd.HasValue)
            {
                updateObject.FirstStrTime = earliestStart.Value;
                updateObject.LastEndTime = latestEnd.Value;
            }
            
            // 执行更新
            if (earliestPlanStart.HasValue || (earliestStart.HasValue && latestEnd.HasValue))
            {
                await _planSharpRep.AsUpdateable()
                    .SetColumns(x => new PlanSharp 
                    { 
                        Jindu = updateObject.Jindu,
                        FirstPStrTime = updateObject.FirstPStrTime,
                        FirstStrTime = updateObject.FirstStrTime,
                        LastEndTime = updateObject.LastEndTime
                    })
                    .Where(x => x.Ch == ch)
                    .ExecuteCommandAsync();
            }
            else
            {
                // 如果没有日期信息，只更新进度
                await _planSharpRep.AsUpdateable()
                    .SetColumns(x => new PlanSharp { Jindu = progress })
                    .Where(x => x.Ch == ch)
                    .ExecuteCommandAsync();
            }
        }
    }

    /// <summary>
    /// 批量更新所有船号进度 🔄
    /// </summary>
    /// <returns></returns>
    [DisplayName("批量更新所有船号进度")]
    [ApiDescriptionSettings(Name = "UpdateAllProgress"), HttpPost]
    public async Task UpdateAllProgress()
    {
        // 获取所有船号
        var shipNumbers = await _planSharpRep.AsQueryable()
            .Where(x => !string.IsNullOrEmpty(x.Ch))
            .Select(x => x.Ch)
            .ToListAsync();

        foreach (var ch in shipNumbers)
        {
            await UpdateShipProgress(ch);
        }
    }
}
